package com.rockontrol.ele.service.model.dbtransaction.impl;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;

import com.rksp.svc.BaseSvc;
import com.rksp.util.DatabaseUtil;
import com.rksp.util.DateUtil;
import com.rksp.util.StringUtil;
import com.rockontrol.ele.pojo.Ma出入库单;
import com.rockontrol.ele.pojo.Ma出入库明细;
import com.rockontrol.ele.pojo.Ma帐套;
import com.rockontrol.ele.pojo.Ma帐套关系;
import com.rockontrol.ele.pojo.Ma物资库存;
import com.rockontrol.ele.pojo.Ma物资消耗台帐;
import com.rockontrol.ele.pojo.Ma盘库报告;
import com.rockontrol.ele.pojo.Ma盘库盈亏明细;
import com.rockontrol.ele.pojo.Ma设备;
import com.rockontrol.ele.pojo.Ma设备台帐;
import com.rockontrol.ele.service.model.dbtransaction.MaStorageTrans;
import com.rockontrol.ele.vo.BaAutoTypeVO;

public class MaStorageTransImpl extends BaseSvc implements MaStorageTrans {
    private Logger log = Logger.getLogger(MaStorageTransImpl.class);
    
    
	public String serviceCreateTheInventoryAllocation(Ma帐套 ia_) {
		List<Ma帐套> list= service.findObjsHQL("from Ma帐套 a where a.单位id='"+ia_.get单位id()+"' and a.类型分类名='"+ia_.get类型分类名()+"' and a.类型='实体库'");
		if(list!=null&&!list.isEmpty())
			return null;
		service.addObj(ia_);
		if(ia_.getChildren()!=null)
			for (Ma帐套 ma:ia_.getChildren()) {
				Ma帐套关系 ma帐套关系=new Ma帐套关系();
				ma帐套关系.set子节点id(ma.getOid());
				ma帐套关系.set父节点id(ia_.getOid());
				service.addObj(ma帐套关系);
			}
		if(ia_.getParent()!=null){
			Ma帐套关系 ma帐套关系=new Ma帐套关系();
			ma帐套关系.set子节点id(ia_.getOid());
			ma帐套关系.set父节点id(ia_.getParent().getOid());
			service.addObj(ma帐套关系);
		}			
		return ia_.getOid();
	}

	public Boolean serviceDeleteTheInventoryAllocation(String id_) {
		service.execHQL("delete from Ma帐套关系 a where a.子节点id='"+id_+"' or a.父节点id='"+id_+"'");
		service.execHQL("delete from Ma物资库存 a where a.帐套id='"+id_+"'");
		service.removeObj(Ma帐套.class,id_);
		return true;
	}

	public Boolean serviceUpdateTheInventoryAllocation(Ma帐套 ia_) {
		service.execHQL("delete from Ma帐套关系 a where a.子节点id='"+ia_.getOid()+"' or a.父节点id='"+ia_.getOid()+"'");
		if(ia_.getChildren()!=null)
			for (Ma帐套 ma:ia_.getChildren()) {
				Ma帐套关系 ma帐套关系=new Ma帐套关系();
				ma帐套关系.set子节点id(ma.getOid());
				ma帐套关系.set父节点id(ia_.getOid());
				service.addObj(ma帐套关系);
			}
		if(ia_.getParent()!=null){
			Ma帐套关系 ma帐套关系=new Ma帐套关系();
			ma帐套关系.set子节点id(ia_.getOid());
			ma帐套关系.set父节点id(ia_.getParent().getOid());
			service.addObj(ma帐套关系);
		}
		service.modifyObj(ia_);
		return true;
	}
	
	private Ma帐套 getTheMaterialAccount (String id_,String type){
		if(StringUtil.isEmpty(id_)||StringUtil.isEmpty(type))
			return null;
		List<Ma帐套> list= service.findObjsHQL("from Ma帐套 a where a.单位id='"+id_+"' and a.类型分类名='"+type+"' and a.类型='实体库'");
		return list.isEmpty()?null:list.get(0);
	}
	
	public String serviceCreateTheInOutDepotReceipt(Ma出入库单 oisr_) {
	    try{
	        Ma帐套 in=null;
	        Ma帐套 out=null;
	    if(StringUtil.isEmpty(oisr_.get入仓库id())){
	    	in=getTheMaterialAccount(oisr_.get入单位id(), oisr_.get类型分类名());
	    }else{
	        in=(Ma帐套)service.findObj(Ma帐套.class,oisr_.get入仓库id());
	    }
        if(!StringUtil.isEmpty(oisr_.get出单位id())){
        out=getTheMaterialAccount(oisr_.get出单位id(), oisr_.get类型分类名());
        oisr_.set出仓库id(out.getOid());
        }
		oisr_.set入仓库id(in.getOid());
		service.addObj(oisr_);
		
		
		//添加明细
		if(oisr_.getStorageDetial()!=null){
			for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
				//修改计划数量和状态
				if(oisr_.isUpdateCount()){
					service.execSQL("update Ma物资计划明细 set 领用状态=(case when 未领数量-"+ma出入库明细.get实际数量().doubleValue()+" <= 0 then '已领' else '未领' end),set 未领数量=未领数量-"+ma出入库明细.get实际数量().doubleValue()+" where 领用状态='未领' and 物资分类ID='"+ma出入库明细.get物资分类id()+"' and 规格型号ID='"+ma出入库明细.get规格型号id()+"'");
				}
				if(oisr_.isUpdateBuy()){
					service.execSQL("update Ma物资计划明细 set 购买状态=(case when 未购数量-"+ma出入库明细.get实际数量().doubleValue()+" <= 0 then '已购' else '未购' end),set 未购数量=未购数量-"+ma出入库明细.get实际数量().doubleValue()+" where 购买状态='未购' and 物资分类ID='"+ma出入库明细.get物资分类id()+"' and 规格型号ID='"+ma出入库明细.get规格型号id()+"'");
				}
				//增加没有的物资库存量
				List<Ma物资库存> list=service.findObjsHQL("from Ma物资库存 a where a.帐套id='"+in.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
				if(list==null||list.isEmpty()){
					Ma物资库存 ma物资库存=new Ma物资库存();
					ma物资库存.set帐套id(in.getOid());
					ma物资库存.set分类编码(ma出入库明细.get分类编码());
					ma物资库存.set单价(ma出入库明细.get实际单价()==null?ma出入库明细.get计划单价():ma出入库明细.get实际单价());
					ma物资库存.set单位(ma出入库明细.get单位());
					ma物资库存.set厂商id(ma出入库明细.get单据id());
					ma物资库存.set厂商名称(ma出入库明细.get厂商名称());
					ma物资库存.set台帐单位(in.get部门单位());
					ma物资库存.set台帐单位id(in.get单位id());
					ma物资库存.set台帐单位编码(in.get单位编码());
					ma物资库存.set库存量(new BigDecimal(0.0));
					ma物资库存.set录入人(oisr_.get录入人姓名());
					ma物资库存.set录入人id(oisr_.get录入人id());
					ma物资库存.set录入时间(oisr_.get录入时间());
					ma物资库存.set物资分类(ma出入库明细.get物资分类());
					ma物资库存.set物资分类id(ma出入库明细.get物资分类id());
					ma物资库存.set规格型号(ma出入库明细.get规格型号());
					ma物资库存.set规格型号id(ma出入库明细.get规格型号id());
					ma物资库存.set规格流水号(ma出入库明细.get规格流水号());
					ma物资库存.set阀值上限(null);
					ma物资库存.set阀值下限(null);
					service.addObj(ma物资库存);
				}
			}
		}
		
		//修改库存量
		if(oisr_.isUpdateInventory()){
			for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
				if(out!=null){
					service.execHQL("update Ma物资库存 a set a.库存量=a.库存量-"+ma出入库明细.get实际数量().doubleValue()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
				}
				if(in!=null){
					double inCount=ma出入库明细.get实际单价().doubleValue()*ma出入库明细.get实际数量().doubleValue();
					service.execHQL("update Ma物资库存 a set a.单价=(a.单价*a.库存量+"+inCount+")/(a.库存量+ "+ma出入库明细.get实际数量().doubleValue()+"), a.库存量=a.库存量+"+ma出入库明细.get实际数量().doubleValue()+"  where a.帐套id='"+in.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
					if("入库".equals(oisr_.get类型()))
						service.execHQL("update Ba规格型号 a set a.单价="+ma出入库明细.get实际单价().doubleValue()+"  where a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.oid='"+ma出入库明细.get规格型号id()+"'");
				}
				List<Ma物资库存> list=service.findSQLAlias("select a.单价 as 单价,a.库存量 as 库存量 from MA_物资库存  a where a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'",Ma物资库存.class);
				ma出入库明细.set库存量(list.get(0).get库存量());
				ma出入库明细.set均价(list.get(0).get单价());
				ma出入库明细.set单据id(oisr_.getOid());
				service.addObj(ma出入库明细);
			}
		}
	    }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException();
        }
		return oisr_.getOid();
	}

	public Boolean serviceDeleteTheInOutDepotReceipt(String id_) {
		Ma出入库单 ma出入库单=(Ma出入库单) service.findObj(Ma出入库单.class, id_);
		if(!("未审批".equals(ma出入库单.get审批状态()) || "".equals(ma出入库单.get审批状态()) ||  ma出入库单.get审批状态()==null))
			return false;
		service.execHQL("delete Ma出入库明细 a where a.单据id='"+id_+"'");
		service.removeObj(ma出入库单);
		return true;
	}

	public Boolean serviceUpdateTheInOutDepotReceipt(Ma出入库单 oisr_) {
		service.execHQL("delete Ma出入库明细 a where a.单据id='"+oisr_.getOid()+"'");
		if(oisr_.getStorageDetial()!=null){
			for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
				ma出入库明细.set单据id(oisr_.getOid());
				if(oisr_.isUpdateCount()){
				    //execSQL是执行的SQL还是HQL 
					service.execSQL("update MA_物资计划明细 set 领用状态=(case when 未领数量-"+ma出入库明细.get实际数量().doubleValue()+" <= 0 then '已领' else '未领' end),set 未领数量=未领数量-"+ma出入库明细.get实际数量().doubleValue()+" where 领用状态='未领' and 物资分类ID='"+ma出入库明细.get物资分类id()+"' and 规格型号ID='"+ma出入库明细.get规格型号id()+"'");
				}
				if(oisr_.isUpdateBuy()){
					service.execSQL("update MA_物资计划明细 set 购买状态=(case when 未购数量-"+ma出入库明细.get实际数量().doubleValue()+" <= 0 then '已购' else '未购' end),set 未购数量=未购数量-"+ma出入库明细.get实际数量().doubleValue()+" where 购买状态='未购' and 物资分类ID='"+ma出入库明细.get物资分类id()+"' and 规格型号ID='"+ma出入库明细.get规格型号id()+"'");
				}
				service.addObj(ma出入库明细);
			}
		}
		//修改库存量
		if(oisr_.isUpdateInventory()){
			Ma帐套 in=getTheMaterialAccount(oisr_.get入单位id(), oisr_.get类型分类名());
			Ma帐套 out=getTheMaterialAccount(oisr_.get出单位id(), oisr_.get类型分类名());
			for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
				if(out!=null)
					service.execHQL("update Ma物资库存 a set a.库存量=a.库存量-"+ma出入库明细.get实际数量().doubleValue()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
				if(in!=null&&"入库".equals(ma出入库明细.get类型())){
//					service.execHQL("update Ma物资库存 a set a.库存量=a.库存量+"+ma出入库明细.get实际数量().doubleValue()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
					service.execHQL("update Ma物资库存 a set a.库存量=a.库存量+"+ma出入库明细.get实际数量().doubleValue()+" where a.帐套id='"+in.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
				}
			}
		}
		service.modifyObj(oisr_);
		return true;
	}

	public Boolean serviceAssignMauipment(Ma出入库单 oisr_) {
		service.execHQL("delete Ma出入库明细 a where a.单据id='"+oisr_.getOid()+"'");
		if(oisr_.getStorageDetial()!=null){
			for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
				ma出入库明细.set单据id(oisr_.getOid());
				service.addObj(ma出入库明细);
			}
		}
		//修改库存量
		if(oisr_.isUpdateInventory()){
			Ma帐套 in=getTheMaterialAccount(oisr_.get入单位id(), oisr_.get类型分类名());
			Ma帐套 out=getTheMaterialAccount(oisr_.get出单位id(), oisr_.get类型分类名());
			for(Ma出入库明细 detail:oisr_.getStorageDetial()){
				if(out!=null)
					service.execHQL("update Ma物资库存 a set a.库存量=a.库存量-"+detail.get实际数量().doubleValue()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+detail.get物资分类id()+"' and a.规格型号id='"+detail.get规格型号id()+"'");
				
				if(detail.getEqIds() != null){
				String[] eqids = detail.getEqIds().split(",");
    				for(String eqid:eqids){
    					Ma设备 ma设备=(Ma设备) service.findObj(Ma设备.class, eqid);
    					ma设备.set设备状态("在用");
    					Ma设备台帐 acc=new Ma设备台帐();
    					acc.set使用单位(in.get部门单位());
    					acc.set使用单位id(in.get单位id());
    					acc.set使用单位编码(in.get单位编码());
    					acc.set发放人id(oisr_.get出人id());
    					acc.set发放人姓名(oisr_.get出人姓名());
    					acc.set帐套id(in.getOid());
    					acc.set状态("在管");
    					acc.set设备id(ma设备.getOid());
    					acc.set设备分类(ma设备.get分类名称());
    					acc.set设备名称(ma设备.get设备名称());
    					acc.set设备型号(ma设备.get设备型号());
    					acc.set设备编码(ma设备.get设备编码());
    					acc.set调入时间(new Timestamp(new Date().getTime()));
    					acc.set领用人id(oisr_.get入人id());
    					acc.set领用人姓名(oisr_.get入人姓名());
    					service.addObj(acc);
    					service.modifyObj(ma设备);
    				}
				}else{
				    
				}
			}
		}
		service.modifyObj(oisr_);
		return true;
	}
    public String serviceCreateTheMaterialCheckInventory(Ma盘库报告 mci){
        try {
            mci.set录入时间(new Timestamp(new Date().getTime()));
            mci.set审批状态("未审批");
            mci.set盈亏状况("正常");
            mci.set状态("已盘完");
            List<Ma盘库盈亏明细> mcInfos = mci.getDetailList();
            service.addObj(mci);
            for(Ma盘库盈亏明细 mcinfo:mcInfos){
                //是否盘点
                if(mcinfo.get盘点数量() == null|| (mcinfo.get盘点数量()+"").equals("")){
                    mcinfo.set盘库记录id(mci.getOid());
                    mci.set状态("未盘完");
                    mcinfo.set盈亏状态("未知");
                    mci.set盈亏状况("未知");
                }else{
                    Double temp;
                    if(mcinfo.get库存数量() != null){
                        temp = mcinfo.get盘点数量().doubleValue() - mcinfo.get库存数量().doubleValue();
                    }else{
                        temp = mcinfo.get盘点数量().doubleValue() ;
                    }
                    if( temp> 0){
                        mcinfo.set盘库记录id(mci.getOid());
                        mcinfo.set盈亏差值(new BigDecimal(temp));
                        mci.set审批状态("未审批");
                        mcinfo.set盈亏状态("盘盈");
                        mci.set盈亏状况("盈亏");
                    }else if(temp < 0){
                        mcinfo.set盘库记录id(mci.getOid());
                        mcinfo.set盈亏差值(new BigDecimal(temp));
                        mci.set审批状态("未审批");
                        mcinfo.set盈亏状态("盘亏");
                        mci.set盈亏状况("盈亏");
                    }else{
                        mcinfo.set盘库记录id(mci.getOid());
                        mcinfo.set盈亏差值(new BigDecimal(temp));
                        mci.set审批状态("未审批");
                        mcinfo.set盈亏状态("正常");
                        mci.set盈亏状况("正常");
                    }
                    mcinfo.set状态("已清点");
               }
            }
            service.modifyObj(mci);
            service.addObjs(mcInfos);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            throw new RuntimeException();
        }
        
        return mci.getOid();
    }

    public Boolean serviceDeleteTheMaterialCheckInventory(String id){
        boolean flag = false;
        try{
            service.execSQL("delete from MA_盘库报告  where OID = '" + id+ "'");
            service.execSQL("delete from MA_盘库盈亏明细  where 盘库记录id = '" +id+ "'");
            flag = true;
        } catch (Exception e) {
            log.info(e.getMessage(),e);
            throw new RuntimeException();
        }
        return flag;
    }

    public Boolean serviceUpdateTheMaterialCheckInventory(Ma盘库报告 mci){
        Boolean flag = false;
        try{ 
//            mci.set审批状态("未审批");
            mci.set盈亏状况("正常");
            mci.set状态("已盘完");
            List<Ma盘库盈亏明细> mcInfos = mci.getDetailList();
            if(mcInfos != null && mcInfos.size()>0){
                for(Ma盘库盈亏明细 mcinfo:mcInfos){
                    //是否盘点
                    if(mcinfo.get盘点数量() == null || (mcinfo.get盘点数量()+"").equals("")){
                        mcinfo.set盘库记录id(mci.getOid());
                        mci.set状态("未盘完");
                        mcinfo.set盈亏状态("未知");
                        mci.set盈亏状况("未知");
                    }else{
                        Double temp = mcinfo.get盘点数量().doubleValue() - mcinfo.get库存数量().doubleValue();
                        if( temp> 0){
                            mcinfo.set盘库记录id(mci.getOid());
                            mcinfo.set盈亏差值(new BigDecimal(temp));
                            mcinfo.set盈亏状态("盘盈");
                            mci.set盈亏状况("盈亏");
                        }else if(temp < 0){
                            mcinfo.set盘库记录id(mci.getOid());
                            mcinfo.set盈亏差值(new BigDecimal(temp));
                            mcinfo.set盈亏状态("盘亏");
                            mci.set盈亏状况("盈亏");
                        }else{
                            mcinfo.set盘库记录id(mci.getOid());
                            mcinfo.set盈亏差值(new BigDecimal(temp));
                            mcinfo.set盈亏状态("正常");
                            mci.set盈亏状况("正常");
                        }
                        mcinfo.set状态("已清点");
                   }
                    mcinfo.set录入时间(new Timestamp(new Date().getTime()));
                }
                service.modifyObjs(mcInfos);
                
            }
            
            if(mci.getBindId()==null){
                if(!StringUtil.isEmpty(mci.get状态()) && !mci.get状态().equals("未盘完")){
                    mci.set审批状态("未审批");
                }
            }
            
            service.modifyObj(mci);
            
            flag = true;
        }catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            throw new RuntimeException();
        }
        return flag;
    }

    public Boolean serviceBalanceAccount(Ma盘库报告 mci){
        Boolean flag = false;
        try{ 
            mci.set平帐日期(new Timestamp(new Date().getTime()));
            List<Ma盘库盈亏明细> mcInfos = mci.getDetailList();
            if(mcInfos!=null){
            for(Ma盘库盈亏明细 mcinfo:mcInfos){
                if(mcinfo.get盈亏差值().compareTo(new BigDecimal(0))!=0){
                    Ma出入库明细 ma = new Ma出入库明细();
                    ma.set物资分类id(mcinfo.get物资分类id());
                    ma.set规格型号id(mcinfo.get规格型号id());
                    if(mcinfo.get盈亏差值().compareTo(new BigDecimal(0))>0){
                        ma.set实际数量(mcinfo.get盈亏差值());
                        updateKc(ma, "+", mci.get帐套id(), mci.get盘库部门id());
                    }else{
                        ma.set实际数量((mcinfo.get盈亏差值()).abs());
                        updateKc(ma, "-", mci.get帐套id(), mci.get盘库部门id());
                    }
                }
            }
            }
            service.modifyObj(mci);
            flag = true;
        }catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            throw new RuntimeException();
        }
        return flag;
    }
    /**
     * 更新库存量
     * @param as
     * @param kcl
     * @return
     */
    private int updateKc(Ma出入库明细 ma,String oper,String inStoreId,String deptId){
        String updatekcsql = " update MA_物资库存  set 库存量 = 库存量"+oper+Math.round(ma.get实际数量().doubleValue()*10000/10000) +
                             " where 帐套ID='" + inStoreId + "' and 台帐单位ID=" +
                             " '"+ deptId + "' and 物资分类ID='"+ma.get物资分类id()+"' and 规格型号ID='"+ma.get规格型号id()+"' ";
        return service.execSQL(updatekcsql);
    }

	public void serviceFinalSettlement() {
		SimpleDateFormat aSimpleDateFormat = new SimpleDateFormat("yyyy-MM");
        GregorianCalendar aGregorianCalendar = new GregorianCalendar();
        aGregorianCalendar.set(Calendar.MONTH, aGregorianCalendar.get(Calendar.MONTH) - 1);
        String nowMonth = DateUtil.date2String(new Date(), "yyyy-MM");
        String nowOfLastMonth = aSimpleDateFormat.format(aGregorianCalendar.getTime());
        String btime = nowOfLastMonth + "-01 00:00:00";
        String etime = DateUtil.date2String(DateUtil.lastDayOfMonth(aGregorianCalendar.getTime()), "yyyy-MM-dd") + " 23:59:59";
        
        StringBuffer sb = new StringBuffer("insert into MA_结算汇总   select ");
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        if("SQLSERVER".equals(dataBaseType)){
                //数据库为sqlserver时执行的操作
            sb.append(" convert(varchar(32),zb.OID) as 仓库id, zb.单位ID as 台帐单位id,zb.单位编码 as 台帐单位编码, zb.部门单位 as 台帐单位,");
            sb.append(" convert(varchar(32),zb.物资分类ID) as 物资分类id,zb.分类编码,zb.物资分类,zb.单位, ");
            sb.append(" convert(varchar(32),zb.规格型号ID) as 规格型号id,zb.规格流水号,zb.规格型号, ");
            sb.append(" isnull(rzb.入帐总数,0) as 入帐总数,  ");
            sb.append(" isnull(czb.出帐总数,0) as 出帐总数, ");
            sb.append(" (isnull(rzb.入帐总数,0)-isnull(czb.出帐总数,0)) as 出入累计, ");
            sb.append(" isnull(rzb.入帐金额,0) as 入帐金额, ");
            sb.append(" isnull(czb.出帐金额,0) as 出帐金额, ");
            sb.append(" isnull(入帐金额,0)-isnull(出帐金额,0) as 金额累计, ");
            sb.append(" isnull(hz.期末数量,0) as 期初数量, "); 
            sb.append(" (isnull(hz.期末数量,0)+isnull((isnull(rzb.入帐总数,0)-isnull(czb.出帐总数,0)),0)) as 期末数量, ");
            sb.append(" isnull(hz.期末金额,0) as 期初金额, ");
            sb.append(" isnull(hz.期末金额,0)+isnull(入帐金额,0)-isnull(出帐金额,0) as 期末金额, ");
            sb.append(" '" + nowOfLastMonth + "' as 结算月,");
            sb.append(" getdate() as 结算日期,");
            sb.append(" getdate() as 录入时间,'机器' as 录入人,'' as 录入人id, zb.类型分类 as 类型分类");
            sb.append(" from ");
            sb.append(" ( ");
            sb.append(" select distinct ck.OID, ");
            sb.append(" zong.类型分类, xh.物资编号 分类编码,xh.分类名称 物资分类,xh.物资分类ID,xh.型号 规格型号,xh.OID 规格型号ID,xh.流水号 规格流水号,  fl.单位");
            sb.append(" ,ck.单位ID,ck.单位编码,ck.部门单位 ");
            sb.append(" from MA_帐套 ck, BA_规格型号 xh, BA_物资分类 fl, ");
            sb.append(" (select  ");
            sb.append(" crk.类型分类, mx.物资分类ID,mx.规格型号ID,crk.出仓库ID,crk.入仓库ID   ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>='" + btime + "' and crk.出入库时间<='" + etime + "' group by crk.类型分类, mx.物资分类ID,mx.规格型号ID,crk.出仓库ID,crk.入仓库ID) zong ");
            sb.append(" where (ck.OID=zong.出仓库ID or ck.OID=zong.入仓库ID )  and zong.规格型号ID=xh.OID and zong.物资分类ID=xh.物资分类ID and zong.物资分类ID=fl.OID  ");
            sb.append(" ) zb ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select  ");
            sb.append(" crk.出仓库ID,mx.规格型号ID,mx.物资分类ID, ");
            sb.append(" isnull(sum(mx.实际数量),0) as 出帐总数,isnull(sum(mx.实际金额),0) as 出帐金额,crk.类型分类 ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>='" + btime + "' and crk.出入库时间<='" + etime + "'  ");
            sb.append(" and crk.出仓库ID is not null ");
            sb.append(" group by crk.出仓库ID,mx.规格型号ID,mx.物资分类ID,crk.类型分类 ");
            sb.append(" ) czb on zb.OID=czb.出仓库ID and zb.物资分类ID=czb.物资分类ID and zb.规格型号ID=czb.规格型号ID ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select "); 
            sb.append(" crk.入仓库ID,mx.规格型号ID,mx.物资分类ID, ");
            sb.append(" isnull(sum(mx.实际数量),0) as 入帐总数,isnull(sum(mx.实际金额),0) as 入帐金额,crk.类型分类 ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>='" + btime + "' and crk.出入库时间<='" + etime + "' "); 
            sb.append(" and crk.入仓库ID is not null and crk.入仓库ID!='' ");
            sb.append(" group by crk.入仓库ID,mx.规格型号ID,mx.物资分类ID,crk.类型分类 ");
            sb.append(" ) rzb on zb.OID=rzb.入仓库ID and zb.物资分类ID=rzb.物资分类ID and zb.规格型号ID=rzb.规格型号ID ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select hz.仓库ID,hz.物资分类ID,hz.规格型号ID,hz.期末数量,hz.期末金额 from MA_结算汇总 hz where convert(varchar(7),hz.结算月,120)='" + nowOfLastMonth + "' ");
            sb.append(" ) hz on zb.OID=hz.仓库ID and zb.物资分类ID=hz.物资分类ID and zb.规格型号ID=hz.规格型号ID ");
        }else if("ORACLE".equals(dataBaseType)){
                //数据库为oracle时执行的操作
            sb.append("  Lower(sys_guid()) as \"oid\",to_char(zb.OID) as \"仓库id\", zb.单位ID as \"台帐单位id\",zb.单位编码 as 台帐单位编码, zb.部门单位 as 台帐单位,");
            sb.append(" to_char(zb.物资分类ID) as \"物资分类id\",zb.分类编码,zb.物资分类,nvl(zb.单位,' ') as 单位, ");
            sb.append(" to_char(zb.规格型号ID) as \"规格型号id\",zb.规格流水号,zb.规格型号, ");
            sb.append(" nvl(rzb.入帐总数,0) as 入帐总数,  ");
            sb.append(" nvl(czb.出帐总数,0) as 出帐总数, ");
            sb.append(" (nvl(rzb.入帐总数,0)-nvl(czb.出帐总数,0)) as 出入累计, ");
            sb.append(" nvl(rzb.入帐金额,0) as 入帐金额, ");
            sb.append(" nvl(czb.出帐金额,0) as 出帐金额, ");
            sb.append(" nvl(入帐金额,0)-nvl(出帐金额,0) as 金额累计, ");
            sb.append(" nvl(hz.期初数量,0) as 期初数量, "); 
            sb.append(" (nvl(hz.期初数量,0)+nvl((nvl(rzb.入帐总数,0)-nvl(czb.出帐总数,0)),0)) as 期末数量, ");
            sb.append(" nvl(hz.期初金额,0) as 期初金额, ");
            sb.append(" nvl(hz.期初金额,0)+nvl(入帐金额,0)-nvl(出帐金额,0) as 期末金额, ");
            sb.append(" to_date('" + nowOfLastMonth + "','yyyy-mm') as 结算月,");
            sb.append(" sysdate as 结算日期,");
            sb.append(" sysdate as 录入时间,'机器' as 录入人,' ' as 录入人id, zb.类型分类 as 类型分类");
            sb.append(" from ");
            sb.append(" ( ");
            sb.append(" select distinct ck.OID, ");
            sb.append(" zong.类型分类, xh.物资编号 分类编码,xh.分类名称 物资分类,xh.物资分类ID,xh.型号 规格型号,xh.OID 规格型号ID,xh.流水号 规格流水号,  fl.单位");
            sb.append(" ,ck.单位ID,ck.单位编码,ck.部门单位 ");
            sb.append(" from MA_帐套 ck, BA_规格型号 xh, BA_物资分类 fl, ");
            sb.append(" (select  ");
            sb.append(" crk.类型分类, mx.物资分类ID,mx.规格型号ID,crk.出仓库ID,crk.入仓库ID   ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>=to_date('" + btime + "','yyyy-mm-dd hh24:mi:ss') and crk.出入库时间<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') group by crk.类型分类, mx.物资分类ID,mx.规格型号ID,crk.出仓库ID,crk.入仓库ID) zong ");
            sb.append(" where (ck.OID=zong.出仓库ID or ck.OID=zong.入仓库ID )  and zong.规格型号ID=xh.OID and zong.物资分类ID=xh.物资分类ID and zong.物资分类ID=fl.OID  ");
            sb.append(" ) zb ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select  ");
            sb.append(" crk.出仓库ID,mx.规格型号ID,mx.物资分类ID, ");
            sb.append(" nvl(sum(mx.实际数量),0) as 出帐总数,nvl(sum(mx.实际金额),0) as 出帐金额,crk.类型分类 ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>=to_date('" + btime + "','yyyy-mm-dd hh24:mi:ss') and crk.出入库时间<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss')  ");
            sb.append(" and crk.出仓库ID is not null ");
            sb.append(" group by crk.出仓库ID,mx.规格型号ID,mx.物资分类ID,crk.类型分类 ");
            sb.append(" ) czb on zb.OID=czb.出仓库ID and zb.物资分类ID=czb.物资分类ID and zb.规格型号ID=czb.规格型号ID ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select "); 
            sb.append(" crk.入仓库ID,mx.规格型号ID,mx.物资分类ID, ");
            sb.append(" nvl(sum(mx.实际数量),0) as 入帐总数,nvl(sum(mx.实际金额),0) as 入帐金额,crk.类型分类 ");
            sb.append(" from MA_出入库明细 mx,MA_出入库单 crk ");
            sb.append(" where crk.OID=mx.单据ID  ");
            sb.append(" and crk.出入库时间>=to_date('" + btime + "','yyyy-mm-dd hh24:mi:ss') and crk.出入库时间<=to_date('" + etime + "','yyyy-mm-dd hh24:mi:ss') "); 
            sb.append(" and crk.入仓库ID is not null and crk.入仓库ID!='' ");
            sb.append(" group by crk.入仓库ID,mx.规格型号ID,mx.物资分类ID,crk.类型分类 ");
            sb.append(" ) rzb on zb.OID=rzb.入仓库ID and zb.物资分类ID=rzb.物资分类ID and zb.规格型号ID=rzb.规格型号ID ");
            sb.append(" left join ");
            sb.append(" ( ");
            sb.append(" select hz.仓库ID,hz.物资分类ID,hz.规格型号ID,hz.期末数量,hz.期末金额 from MA_结算汇总 hz where to_char(hz.结算月,'yyyy-mm')='" + nowOfLastMonth + "' ");
            sb.append(" ) hz on zb.OID=hz.仓库ID and zb.物资分类ID=hz.物资分类ID and zb.规格型号ID=hz.规格型号ID ");
        }
        System.out.println(sb.toString());
        service.execSQL(sb.toString());
	}

    public String ServiceCreateTheSendBackReceipt(Ma出入库单 oisr_){
        try{
        Ma帐套 in=null;
        Ma帐套 out=null;
        
           
        if(StringUtil.isEmpty(oisr_.get入仓库id())){
        in=getTheMaterialAccount(oisr_.get入单位id(), oisr_.get类型分类名());
        }else{
            in=(Ma帐套)service.findObj(Ma帐套.class,oisr_.get入仓库id());
        }
        if(!StringUtil.isEmpty(oisr_.get出单位id())){
        out=getTheMaterialAccount(oisr_.get出单位id(), oisr_.get类型分类名());
        oisr_.set出仓库id(out.getOid());
        }
        oisr_.set入仓库id(in.getOid());
        if(StringUtil.isEmpty(oisr_.getOid())){
        	service.addObj(oisr_);
        }else{
        	service.execHQL("delete Ma出入库明细 a where a.单据id='"+oisr_.getOid()+"'");
        }
        
        //添加明细
        if(oisr_.getStorageDetial()!=null){
            for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
                ma出入库明细.set单据id(oisr_.getOid());
                service.addObj(ma出入库明细);
                
                //增加没有的物资库存量
                List<Ma物资库存> list=service.findObjsHQL("from Ma物资库存 a where a.帐套id='"+in.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
                if(list==null||list.isEmpty()){
                    Ma物资库存 ma物资库存=new Ma物资库存();
                    ma物资库存.set帐套id(in.getOid());
                    ma物资库存.set分类编码(ma出入库明细.get分类编码());
                    ma物资库存.set单价(ma出入库明细.get实际单价()==null?ma出入库明细.get计划单价():ma出入库明细.get实际单价());
                    ma物资库存.set单位(ma出入库明细.get单位());
                    ma物资库存.set厂商id(ma出入库明细.get单据id());
                    ma物资库存.set厂商名称(ma出入库明细.get厂商名称());
                    ma物资库存.set台帐单位(in.get部门单位());
                    ma物资库存.set台帐单位id(in.get单位id());
                    ma物资库存.set台帐单位编码(in.get单位编码());
                    ma物资库存.set库存量(new BigDecimal(0.0));
                    ma物资库存.set录入人(oisr_.get录入人姓名());
                    ma物资库存.set录入人id(oisr_.get录入人id());
                    ma物资库存.set录入时间(oisr_.get录入时间());
                    ma物资库存.set物资分类(ma出入库明细.get物资分类());
                    ma物资库存.set物资分类id(ma出入库明细.get物资分类id());
                    ma物资库存.set规格型号(ma出入库明细.get规格型号());
                    ma物资库存.set规格型号id(ma出入库明细.get规格型号id());
                    ma物资库存.set规格流水号(ma出入库明细.get规格流水号());
                    ma物资库存.set阀值上限(null);
                    ma物资库存.set阀值下限(null);
                    service.addObj(ma物资库存);
                }
            }
        }
        
        //修改库存量
        if(oisr_.isUpdateInventory()){
            for(Ma出入库明细 ma出入库明细:oisr_.getStorageDetial()){
                if(out!=null)
                    service.execHQL("update Ma物资库存 a set a.库存量=a.库存量-"+ma出入库明细.get退回数量()+" where a.帐套id='"+in.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
                if(in!=null){
                    service.execHQL("update Ma物资库存 a set a.库存量=a.库存量+"+ma出入库明细.get退回数量().subtract(ma出入库明细.get退回厂家数量())+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ma出入库明细.get物资分类id()+"' and a.规格型号id='"+ma出入库明细.get规格型号id()+"'");
                }
            }
        }
        if(StringUtil.isEmpty(oisr_.getOid())){
        	service.modifyObj(oisr_);
        }
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException();
        }
        return oisr_.getOid();
    }

	public String serviceCreateConsumeAcc(Ma物资消耗台帐 ca_) {
		service.addObj(ca_);
		
		Ma帐套 out=getTheMaterialAccount(ca_.get消耗单位id(), ca_.get类型分类());
		if(out!=null)
			service.execHQL("update Ma物资库存 a set a.库存量=a.库存量-"+ca_.get消耗数量()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ca_.get物资分类id()+"' and a.规格型号id='"+ca_.get规格型号id()+"'");
		
		return ca_.getOid();
	}

	public Boolean serviceDeleteConsumeAcc(String id_) {
		Ma物资消耗台帐 ca_=(Ma物资消耗台帐) service.findObj(Ma物资消耗台帐.class, id_);
		Ma帐套 out=getTheMaterialAccount(ca_.get消耗单位id(), ca_.get类型分类());
		if(out!=null)
			service.execHQL("update Ma物资库存 a set a.库存量=a.库存量+"+ca_.get消耗数量()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ca_.get物资分类id()+"' and a.规格型号id='"+ca_.get规格型号id()+"'");
		
		service.removeObj(Ma物资消耗台帐.class,id_);
		return true;
	}

	public Boolean serviceUpdateConsumeAcc(Ma物资消耗台帐 ca_) {
		Ma帐套 out=getTheMaterialAccount(ca_.get消耗单位id(), ca_.get类型分类());
		if(out!=null)
			service.execHQL("update Ma物资库存 a set a.库存量=a.库存量+(select 消耗数量 from Ma物资消耗台帐 b where b.oid='"+ca_.getOid()+"')-"+ca_.get消耗数量()+" where a.帐套id='"+out.getOid()+"' and a.物资分类id='"+ca_.get物资分类id()+"' and a.规格型号id='"+ca_.get规格型号id()+"'");
		
		service.modifyObj(ca_);
		return true;
	}
}
